# -*- coding: utf-8 -*-
from src.com.bsjs.util.base_util import BaserUtil
from src.com.bsjs.util.redis_util import RedisUtil
from src.com.bsjs.util.sql_util import MySQLUtils
from src.com.ydzy.util.file_util import FileClient

table_sqls=[]
table_attr_sqls=[]

table_row_sqls=[]
table_data_sqls=[]


class SQL:
    def __init__(self):
        self.mysql_client = MySQLUtils()
        self.file_client = FileClient()
        self.base_client = BaserUtil()
        self.redis_client = RedisUtil()
        self.import_table='param_data'

    def get_types(self):
        types=self.redis_client.get_value("types")
        if types!=None and len(types)>0:
            return self.base_client.json2obj(types)
        sql="SELECT distinct type FROM `param_data`"
        datas=self.mysql_client.retrieve_sql(sql=sql)
        types=[]
        for data in datas:
            types.append(data[0])
        self.redis_client.set_value("types",self.base_client.obj2json(types))
        return types

    def load_data(self, type):
        # 生成表sql
        condition=f"type ='{type}'"
        datas=self.mysql_client.retrieve_json(table=self.import_table,condition=condition)
        table_id=self.generate_table_data(type,datas[0])

        #生成属性sql
        attr_dict = self.generate_attr_data(type, datas[0],table_id)

        #生成行sql
        self.generate_rows_data(type, datas, table_id,attr_dict)




    def generate_table_data(self, type,data):
        increment_key= 'param_table_key'
        id=self.redis_client.increment_value(increment_key)
        table='customs_params'
        columns='id,code,name'
        values=str(id)+","+"'"+type+"'"+','+"'"+self.escape_single_quotes(data['type_zh'])+"'"
        sql = f"INSERT INTO {table} ({columns}) VALUES ({values}) ;"
        table_sqls.append(sql)
        return id

    def generate_attr_data(self, type, data, table_id):
        table = 'customs_attribute'
        increment_key= 'param_attr_key'
        columns = 'id,params_id,code,name,is_search,data_type,order_num'
        field_dicts= self.base_client.json2obj(data['field_dict'])
        headers = [item['colname'] for item in field_dicts]
        field_dict={}
        for item in field_dicts:
            field_dict[item['colname']]=item['coldisplay']

        attr_dict={}
        for head in headers:
            id = self.redis_client.increment_value(increment_key)
            values =\
             str(id)  +","+\
             str(table_id)  +","+\
            "'" +head+ "'"+","+ \
            "'" +self.escape_single_quotes(field_dict[head].strip())  + "'"+","+ \
            "'" +"1" + "'"+","+ \
            "'" +"1" + "'"+","+ \
            "'" +str(headers.index(head)+1)+ "'"
            sql = f"INSERT INTO {table} ({columns}) VALUES ({values}) ;"
            table_attr_sqls.append(sql)
            attr_dict[head]=id
        return attr_dict

    def generate_rows_data(self, type, datas, table_id, attr_dict):
        increment_key='param_row_key'
        table = 'customs_substance'
        columns = 'id,params_id'
        for data in datas:
            id = self.redis_client.increment_value(increment_key)

            values = str(id) + "," + \
                 str(table_id)
            sql = f"INSERT INTO {table} ({columns}) VALUES ({values}) ;"
            table_row_sqls.append(sql)
            self.generate_datas_data(type, data, table_id, attr_dict,id)



    #生成行sql
    def generate_datas_data(self, type, data, table_id, attr_dict, row_id):
        increment_key= 'param_data_key'
        table = 'customs_info'
        columns = 'id,params_id,substance_id,attribute_id,info'
        field_data = self.base_client.json2obj(self.base_client.urldecode(data['field_data']))
        field_dicts = self.base_client.json2obj(data['field_dict'])
        headers = [item['colname'] for item in field_dicts]
        for head in headers:
            id = self.redis_client.increment_value(increment_key)
            values = "" + str(id) + "" + ","\
                    +"" +  str(table_id) + "" + ","\
                    +"" + str(row_id) + "" + "," \
                    + "'" + str(attr_dict[head]).strip() + "'" + "," \
                    + "'" + self.escape_single_quotes(field_data.get(head,'').strip() ) + "'"
            sql = f"INSERT INTO {table} ({columns}) VALUES ({values}) ;"
            table_data_sqls.append(sql)

    def generate_sql_file(self,index):
        result=''
        desc1="--  param_table表"+"\n"
        result=desc1+"\n".join(table_sqls)+"\n\n"

        desc2="--  param_attr表"+"\n"
        result = result+desc2+"\n".join(table_attr_sqls)+"\n\n"

        desc3="--  param_row表"+"\n"
        result = result + desc3 + "\n".join(table_row_sqls) + "\n\n"

        desc4="--  param_data表"+"\n"
        result = result + desc4 + "\n".join(table_data_sqls) + "\n\n"

        self.file_client.write(file_path='./tmp/data'+index+'.sql',line=result)

    def escape_single_quotes(self, input_string):
        """
        将输入字符串中的单引号转义为 \'
        :param input_string: 输入的字符串
        :return: 转义后的字符串
        """
        return input_string.replace("'", "\\'")


def main():
    this = SQL()
    types=this.get_types()
    for type in types:
        this.load_data(type)
        print(f"{type}-->总页码为{len(types)},当前页码：{types.index(type)+1}当前完成进度为{(((types.index(type) + 1)*100 / len(types)))}%")
         # 生成sql数据文件
        this.generate_sql_file(str(types.index(type)+1))
        global  table_sqls
        global  table_attr_sqls
        global  table_row_sqls
        global  table_data_sqls
        table_sqls = []
        table_attr_sqls = []
        table_row_sqls = []
        table_data_sqls = []








if __name__ == '__main__':
    main()